using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using QLPMT_DTO;

namespace QLPMT_DAO
{
   public class BaoCaoSuDungThuocDAO
    {
       DataProvider Provider;
       public BaoCaoSuDungThuocDAO()
       {
           Provider = new DataProvider();
       }
       public List<BaoCaoSuDungThuoc> GetAll()
       {
           Provider.Connect();
           List<BaoCaoSuDungThuoc> list = new List<BaoCaoSuDungThuoc>();
           string sql = "select * from BaoCaoSuDungThuoc";
           SqlDataReader reader = Provider.ExecuteReader(sql);
           while (reader.Read())
           {
               string ma = reader.GetString(0);
               string mathuoc = reader.GetString(1);
               int soluog = reader.GetInt32(2);
               int solandung = reader.GetInt32(3);
               string madonvi = reader.GetString(4);
               BaoCaoSuDungThuoc dto = new BaoCaoSuDungThuoc(ma, mathuoc, madonvi, solandung, soluog);
               list.Add(dto);
           }
           Provider.Disconnect();
           return list;
       }
       public List<BaoCaoSuDungThuoc> DanhSachThuocSuDungTrongThang(int thang)
       {
          
           List<ChiTietPhieuKham> listPK = new ChiTietPhieuKhamDAO().DanhSachThuocVaDonVi(thang);
           List<BaoCaoSuDungThuoc> listBC =GetAll();
           int mabc = 0;
           if (listBC.Count > 0)
           {
               mabc = Convert.ToInt32(listBC[listBC.Count - 1].Ma);
           }
           List<BaoCaoSuDungThuoc> list = new List<BaoCaoSuDungThuoc>(); 
           foreach (ChiTietPhieuKham X in listPK)
           {
               mabc += 1;
               string strmabc = mabc.ToString();
               Provider.Connect();
               string sql = "select count(ChiTietPhieuKham.MaLoaiThuoc), Sum(SoLuong) from ChiTietPhieuKham,PhieuKham,KhamBenh where ChiTietPhieuKham.MaPhieuKham=PhieuKham.MaPhieuKham and PhieuKham.MaKhamBenh=KhamBenh.MaKhamBenh and ChiTietPhieuKham.MaLoaiThuoc='" +X.Maloaithuoc+"' and ChiTietPhieuKham.MaDonVi='"+X.Madonvi+"' and DatePart(mm,ngaykham)="+thang;
               SqlDataReader reader = Provider.ExecuteReader(sql);
               string mathuoc = X.Maloaithuoc;
               string madv = X.Madonvi;
               reader.Read();
               int solandung =Convert.ToInt32( reader[0]);
               int soluong = Convert.ToInt32(reader[1]);
               BaoCaoSuDungThuoc dto = new BaoCaoSuDungThuoc(strmabc,mathuoc,madv,solandung,soluong);
               list.Add(dto);
               Provider.Disconnect();
           }
           return list;
       }
    }
}
